<?php
//include APPLICATION_PATH."/models/base/ProvinceBase.php";
/* 
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
class Model_Report5{
    
    public function fetchAllReport($nam, $agent_ids=""){
        $db = Zend_Db_Table::getDefaultAdapter();
        $andWhere = "";
        if(strlen($agent_ids)>0){
            $andWhere = " AND order.store_id IN (".$agent_ids.")";           
        }
        //$select=$db->select()
               // ->from('province')
                //->//where('status=1');
        $stmt=$db->query("SELECT T.id, T.name_customer, 
                IFNULL(T1.quantity,0) as quarter1, IFNULL(T2.quantity,0) as quarter2, 
                IFNULL(T3.quantity,0) as quarter3, IFNULL(T4.quantity,0) as quarter4,                 
                IFNULL(T1.quantity,0) + IFNULL(T2.quantity,0) + IFNULL(T3.quantity,0) + IFNULL(T4.quantity,0)  as total_quantity,
                ROUND(IFNULL(T1.charges,0) + IFNULL(T2.charges,0) + IFNULL(T3.charges,0) + IFNULL(T4.charges,0),1) as charges
                FROM
                (
                SELECT DISTINCT agent.id, agent.name AS name_customer
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2  AND YEAR( order_detail.order_created ) =".$nam."
                    ".$andWhere."                               
                ) AS T 
                LEFT JOIN
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =1 OR MONTH( order_detail.order_created ) =2 OR MONTH( order_detail.order_created ) =3)
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T1 ON T.id = T1.id
                LEFT JOIN 
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =4 OR MONTH( order_detail.order_created ) =5 OR MONTH( order_detail.order_created ) =6)
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T2 ON T.id = T2.id
                LEFT JOIN 
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =7 OR MONTH( order_detail.order_created ) =8 OR MONTH( order_detail.order_created ) =9 )
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T3 ON T.id = T3.id
                LEFT JOIN 
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =10 OR MONTH( order_detail.order_created ) =11 OR MONTH( order_detail.order_created ) =12  )
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T4 ON T.id = T4.id
                ");
        $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
        $stmt->closeCursor();
        $entries   = array();
        foreach ($rows as $row) {
            //$entry = new Model_Province();
            array_push($entries, $row);
            //$entries[] = $entry;
        }
        return $entries;
    }
    
    public function fetchAllReportExport($nam, $agent_ids){
        $db = Zend_Db_Table::getDefaultAdapter();
        $andWhere = "";
        if(strlen($agent_ids)>0){
            $andWhere = " AND order.store_id IN (".$agent_ids.")";           
        }
        //$select=$db->select()
               // ->from('province')
                //->//where('status=1');
        $stmt=$db->query("SELECT T.id, T.name_customer, 
                IFNULL(T1.quantity,0) as quarter1, IFNULL(T2.quantity,0) as quarter2, 
                IFNULL(T3.quantity,0) as quarter3, IFNULL(T4.quantity,0) as quarter4,                 
                IFNULL(T1.quantity,0) + IFNULL(T2.quantity,0) + IFNULL(T3.quantity,0) + IFNULL(T4.quantity,0)  as total_quantity,
                ROUND(IFNULL(T1.charges,0) + IFNULL(T2.charges,0) + IFNULL(T3.charges,0) + IFNULL(T4.charges,0),1) as charges
                FROM
                (
                SELECT DISTINCT agent.id, agent.name AS name_customer
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and agent.agent_status !=3 AND YEAR( order_detail.order_created ) =".$nam."
                    ".$andWhere."                               
                            
                ) AS T 
                LEFT JOIN
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =1 OR MONTH( order_detail.order_created ) =2 OR MONTH( order_detail.order_created ) =3)
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T1 ON T.id = T1.id
                LEFT JOIN 
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =4 OR MONTH( order_detail.order_created ) =5 OR MONTH( order_detail.order_created ) =6)
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T2 ON T.id = T2.id
                LEFT JOIN 
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =7 OR MONTH( order_detail.order_created ) =8 OR MONTH( order_detail.order_created ) =9 )
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T3 ON T.id = T3.id
                LEFT JOIN 
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =10 OR MONTH( order_detail.order_created ) =11 OR MONTH( order_detail.order_created ) =12  )
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T4 ON T.id = T4.id
                ");
        $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
        $stmt->closeCursor();
        $entries   = array();
        foreach ($rows as $row) {
            //$entry = new Model_Province();
            array_push($entries, $row);
            //$entries[] = $entry;
        }
        return $entries;
    }
    
    public function fetchAllReportExportByKinhDoanh($nam,$admin_id, $agent_ids){
        $db = Zend_Db_Table::getDefaultAdapter();
        $andWhere = "";
        if(strlen($agent_ids)>0){
            $andWhere = " AND order.store_id IN (".$agent_ids.")";           
        }
        //$select=$db->select()
               // ->from('province')
                //->//where('status=1');
        $stmt=$db->query("SELECT T.id, T.name_customer, 
                IFNULL(T1.quantity,0) as quarter1, IFNULL(T2.quantity,0) as quarter2, 
                IFNULL(T3.quantity,0) as quarter3, IFNULL(T4.quantity,0) as quarter4,                 
                IFNULL(T1.quantity,0) + IFNULL(T2.quantity,0) + IFNULL(T3.quantity,0) + IFNULL(T4.quantity,0)  as total_quantity,
                ROUND(IFNULL(T1.charges,0) + IFNULL(T2.charges,0) + IFNULL(T3.charges,0) + IFNULL(T4.charges,0),1) as charges
                FROM
                (
                SELECT DISTINCT agent.id, agent.name AS name_customer
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and agent.agent_status !=3 and agent.province_id in (Select admin_area.area_id from admin left join admin_area on admin.id= admin_area.admin_id where admin.id='".$admin_id."')           
                     AND YEAR( order_detail.order_created ) =".$nam." ".$agent_ids."
                ) AS T 
                LEFT JOIN
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =1 OR MONTH( order_detail.order_created ) =2 OR MONTH( order_detail.order_created ) =3)
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T1 ON T.id = T1.id
                LEFT JOIN 
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =4 OR MONTH( order_detail.order_created ) =5 OR MONTH( order_detail.order_created ) =6)
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T2 ON T.id = T2.id
                LEFT JOIN 
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =7 OR MONTH( order_detail.order_created ) =8 OR MONTH( order_detail.order_created ) =9 )
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T3 ON T.id = T3.id
                LEFT JOIN 
                (SELECT agent.id, agent.name AS name_customer, SUM( order_detail.quantity ) AS quantity, SUM( order_detail.charges ) AS charges
                FROM  `order` 
                INNER JOIN  `order_detail` ON order.order_no = order_detail.order_id
                INNER JOIN  `agent` ON order.store_id = agent.id
                WHERE order_detail.order_status_id = 2 and
                 ( MONTH( order_detail.order_created ) =10 OR MONTH( order_detail.order_created ) =11 OR MONTH( order_detail.order_created ) =12  )
                AND YEAR( order_detail.order_created ) =".$nam."
                GROUP BY agent.id, agent.name) AS T4 ON T.id = T4.id
                ");
        $rows = $stmt->fetchAll(PDO::FETCH_CLASS);
        $stmt->closeCursor();
        $entries   = array();
        foreach ($rows as $row) {
            //$entry = new Model_Province();
            array_push($entries, $row);
            //$entries[] = $entry;
        }
        return $entries;
    }
}

